Leftmost Prefix Rule

contents

다음은 복합 인덱스(Composite Index)의 Leftmost Prefix Rule(좌측 접두사 규칙) 에 대한 설명입니다.

멀티 컬럼 쿼리의 성능을 최적화하려면 반드시 이해해야 할 중요한 규칙입니다. 이 규칙을 어기면 비싸게 만든 인덱스가 무용지물이 됩니다.

1. 개념: "전화번호부" 비유

복합 인덱스(여러 열에 걸친 인덱스)를 이해하기 위해 물리적인 전화번호부를 상상해 보세요.

이 전화번호부는 성(Last Name) 으로 먼저 정렬되고, 그 안에서 이름(First Name), 마지막으로 전화번호(Phone Number) 순으로 정렬되어 있습니다.

인덱스 정의: (성, 이름, 전화번호)

이러한 정렬 순서 때문에:

• 쉬움: "김(Kim)"씨 성을 가진 사람을 찾는 것 (가장 왼쪽 열).
• 쉬움: "김(Kim)"씨 성을 가진 "철수(Cheolsu)"를 찾는 것 (첫 번째 + 두 번째 열).
• 불가능: 이름이 "철수"인 사람만 바로 찾는 것 (중간 열). 왜냐하면 "철수"는 "이철수", "박철수", "최철수" 등 여기저기에 흩어져 있기 때문입니다. 책이 이름 순으로 정렬되어 있지 않으니까요.

이것이 바로 B-Tree 복합 인덱스의 작동 방식입니다.

2. 규칙의 작동 원리

데이터베이스는 쿼리가 인덱스의 가장 왼쪽부터 시작하여 끊김 없이 오른쪽으로 이어질 때만 인덱스를 사용할 수 있습니다.

Users 테이블에 다음과 같은 인덱스가 있다고 가정해 봅시다:

CREATE INDEX idx_user_data ON Users (부서, 직급, 나이);

순서: 1. 부서 -> 2. 직급 -> 3. 나이

A. ✅ 인덱스 작동 성공 (효율적)

1. 1번째 열 매칭:
   • WHERE 부서 = '영업팀'
   • (인덱스가 완벽하게 작동합니다).
2. 1번째 + 2번째 열 매칭:
   • WHERE 부서 = '영업팀' AND 직급 = '과장'
   • (영업팀을 찾고, 그 안에서 과장을 바로 찾아냅니다).
3. 3개 열 모두 매칭:
   • WHERE 부서 = '영업팀' AND 직급 = '과장' AND 나이 = 30
   • (특정 데이터로 바로 점프합니다).

B. ❌ 인덱스 사용 실패 (비효율적)

1. 가장 왼쪽을 건너뜀 (치명적):
   • WHERE 직급 = '과장'
   • 결과: Full Table Scan (전체 스캔). 목록이 '부서' 위주로 정렬되어 있기 때문에, '과장'만 따로 뽑아낼 수 없습니다.
2. 가장 왼쪽을 건너뜀 (중간부터 시작):
   • WHERE 나이 = 30
   • 결과: Full Table Scan.

C. ⚠️ "Gap(구멍)" 발생 (부분 사용)

1. 중간을 건너뜀:
   • WHERE 부서 = '영업팀' AND 나이 = 30
   • 결과: 데이터베이스는 부서에 대해서만 인덱스를 사용합니다.
   • '영업팀' 데이터를 빠르게 찾습니다. 하지만 '영업팀' 내부 데이터는 직급 순으로 정렬되어 있지, 나이 순이 아닙니다. 따라서 DB는 찾아낸 영업팀 데이터를 전부 스캔하여 나이가 30인 사람을 골라내야 합니다. 나이 인덱스는 여기서 무용지물입니다.

3. "범위(Range)" 중단 조건

많은 개발자가 놓치는 미묘한 규칙입니다.

어떤 컬럼에 대해 범위 쿼리(>, <, BETWEEN, LIKE 'abc%')를 수행하는 순간, 그 뒤에 오는 컬럼들은 더 이상 인덱스를 타지 않습니다.

예시 인덱스: (부서, 직급, 나이)

쿼리:

SELECT * FROM Users
WHERE 부서 = '영업팀'       -- 정확한 일치 (인덱스 사용됨)
  AND 직급 > '대리'        -- 범위 일치 (인덱스 사용됨)
  AND 나이 = 30;          -- 정확한 일치 (인덱스 무시됨 ❌)

이유는?

'대리'보다 높은 직급의 범위를 선택하는 순간, 그 범위 안에 있는 데이터들은 더 이상 나이 순으로 예쁘게 정렬되어 있지 않기 때문입니다 (직급별로 나이가 섞여 있음). 따라서 나이=30을 찾으려면 범위를 다 뒤져야 합니다.

4. 요약 표

references